package farmacia.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import conexao.banco.CriaConexao;




import farmacia.abstrata.Pessoa;
import farmacia.entidade.pessoa.Cliente;





public class ClienteDao {
	private Connection conexao;

    public ClienteDao() throws SQLException {
        this.conexao = CriaConexao.getConexao();

    }
             // Metodo de inserção em banco de dados mysql
    public void adicionar(Cliente c1) throws SQLException {
        String sql = "insert  into tb_substancias("
                + "fi_substancia) values(?)";
        
        PreparedStatement stmt = conexao.prepareStatement(sql);

        stmt.setString(1, c1.getNome());
        
        stmt.executeUpdate();
        stmt.close();

    }

    public void excluir(Cliente c1) throws SQLException {
        
        String sql = "delete from tb_clientes where fi_pk_id= ?";
        
        PreparedStatement stmt = this.conexao.prepareStatement(sql);
        stmt.setInt(1, c1.getFi_id());
        
        stmt.execute();
        stmt.close();
    }

    public void alterar(Cliente c1) throws SQLException {
        String sql = "UPDATE tb_clientes SET "
                + "nome=?"
                +"WHERE fi_pk_id=?";
        
        PreparedStatement stmt = conexao.prepareStatement(sql);
        
                  // setar valores
           
    
        stmt.setInt(11, c1.getFi_id());
        
        
        stmt.executeUpdate();
        stmt.close();

    }

    
    
    
    
    public List<Cliente> getLista(String nome) throws SQLException{
        
    String sql = "select * from tb_usuarios where nome like ? ORDER BY nome ASC";
    PreparedStatement stmt = this.conexao.prepareStatement(sql);
    stmt.setString(1, nome);
    ResultSet rs = stmt.executeQuery();
    
    List <Cliente> listaClientes = new ArrayList<Cliente>();
    
    while (rs.next()){
     
    	Cliente c1 = new Cliente();
                c1.setFi_id(Integer.valueOf(rs.getString("fi_pk_id")));
                c1.setNome(rs.getString("fi_nome"));
                
                 
        listaClientes.add(c1);
    }    
    rs.close();
    stmt.close();
    
    return listaClientes;
     
    }
    public List<Cliente> getLista() throws SQLException{
        
        String sql = "select fi_nome from tb_clientes";
        PreparedStatement stmt = this.conexao.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        
        List <Cliente> listaClientes = new ArrayList<Cliente>();
        
        while (rs.next()){
         
        	Cliente c1 = new Cliente();
                    c1.setFi_id(Integer.valueOf(rs.getString("fi_pk_id")));
                    c1.setNome(rs.getString("fi_nome"));
                    
                     
            listaClientes.add(c1);
        }    
        rs.close();
        stmt.close();
        
        return listaClientes;
         
        }
}
